package top.lishuoboy.jdbc;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;

public class JdbcTest {

    private String urlMysql = "jdbc:mysql://localhost:3306/lishuoboy-jdbc";
    private String urlMysqlBatch = "jdbc:mysql://localhost:3306/lishuoboy-jdbc?rewriteBatchedStatements=true";
    private String userMysql = "root";
    private String passwordMysql = "1234";
    //    private String driveClassMysql = "com.mysql.jdbc.Driver";  // mysql5
    private String driveClassMysql = "com.mysql.cj.jdbc.Driver";  // mysql8

    private String urlOracle = "jdbc:oracle:thin:@localhost:1521:ORCL";
    private String userOracle = "jdbc";
    private String passwordOracle = "1234";
    private String driveClassOracle = "oracle.jdbc.driver.OracleDriver";

    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");

    public static void main(String[] args) {
        JdbcTest t = new JdbcTest();

//        t.testMysql4();
//        t.testOracle4();
        t.testMysql7();
//        t.testOracle7();
//        t.testMysql8();
//        t.testOracle8();
//        t.testMysql9();
//        t.testMysql10();

    }


    /**
     * <h1>测试不关闭Connection、Statement、ResultSet效果</h1><br/>
     * <br/>
     * <b>结论：</b><br/>
     * ① Connection：有数量限制，必须关闭。<br/>
     * ② Statement：Oracle执行过.executeQuery（）有数量限制，必须关闭。Mysql无数量限制<br/>
     * ③ ResultSet：无数量限制<br/>
     */
    private void testMysql4() {
        System.out.println(getCurMethodName(Thread.currentThread().getStackTrace()[1]));

        int i;

        // 一、循环创建ResultSet
        i = 0;
        Connection conn1 = null;
        Statement st1 = null;
        ResultSet rs1 = null;
        try {
            // 加载驱动
            Class.forName(driveClassMysql);

            conn1 = DriverManager.getConnection(urlMysql, userMysql, passwordMysql);
            st1 = conn1.createStatement(); // 创建Statement
            while (true) {
                i++;
                if (i % 10000 == 0) {
                    System.out.println("创建ResultSet，i==" + i);
                }
                if (i > 100000)
                    break;
                rs1 = st1.executeQuery("select 1 from dual"); // 创建ResultSet
                while (rs1.next()) {
                }
            }
        } catch (Exception e) {
            System.err.println("创建ResultSet，i==" + i);
            e.printStackTrace();
        } finally {
            try {
                if (conn1 != null)
                    conn1.close();
                if (st1 != null)
                    st1.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        // 二、循环创建Statement
        i = 0;
        Connection conn2 = null;
        Statement st2 = null;
        try {
            // 加载驱动
            Class.forName(driveClassMysql);

            conn2 = DriverManager.getConnection(urlMysql, userMysql, passwordMysql);
            while (true) {
                i++;
                if (i % 10000 == 0) {
                    System.out.println("创建Statement，i==" + i);
                }
                if (i > 100000)
                    break;
                st2 = conn2.createStatement(); // 创建Statement
                st2.executeQuery("select 1 from dual"); // 创建ResultSet
            }
        } catch (Exception e) {
            System.err.println("创建Statement，i==" + i);
            e.printStackTrace();
        } finally {
            try {
                if (conn2 != null)
                    conn2.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        // 三、循环创建Connection
        i = 0;
        try {
            // 加载驱动
            Class.forName(driveClassMysql);

            while (true) {
                i++;
                if (i % 100 == 0) {
                    System.out.println("创建Connection，i==" + i);
                }
                DriverManager.getConnection(urlMysql, userMysql, passwordMysql);// 创建Connection
            }
        } catch (Exception e) {
            System.err.println("创建Connection，i==" + i);
            e.printStackTrace();
        }

    }

    /**
     * <h1>测试不关闭Connection、Statement、ResultSet效果</h1><br/>
     * <br/>
     * <b>结论：</b><br/>
     * ① Connection：有数量限制，必须关闭。<br/>
     * ② Statement：Oracle执行过.executeQuery（）有数量限制，必须关闭。Mysql无数量限制<br/>
     * ③ ResultSet：无数量限制<br/>
     */
    private void testOracle4() {
        System.out.println(getCurMethodName(Thread.currentThread().getStackTrace()[1]));

        int i;

        // 一、循环创建ResultSet
        i = 0;
        Connection conn1 = null;
        Statement st1 = null;
        ResultSet rs1 = null;
        try {
            // 加载驱动
            Class.forName(driveClassOracle);

            conn1 = DriverManager.getConnection(urlOracle, userOracle, passwordOracle);
            st1 = conn1.createStatement(); // 创建Statement
            while (true) {
                i++;
                if (i % 10000 == 0) {
                    System.out.println("创建ResultSet，i==" + i);
                }
                if (i > 100000)
                    break;
                rs1 = st1.executeQuery("select 1 from dual"); // 创建ResultSet
                while (rs1.next()) {
                }
            }
        } catch (Exception e) {
            System.err.println("创建ResultSet，i==" + i);
            e.printStackTrace();
        } finally {
            try {
                if (st1 != null)
                    st1.close();
                if (conn1 != null)
                    conn1.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        // 二、循环创建Statement
        i = 0;
        Connection conn2 = null;
        Statement st2 = null;
        try {
            // 加载驱动
            Class.forName(driveClassOracle);

            conn2 = DriverManager.getConnection(urlOracle, userOracle, passwordOracle);
            while (true) {
                i++;
                if (i % 10000 == 0) {
                    System.out.println("创建Statement，i==" + i);
                }
                if (i > 100000)
                    break;
                st2 = conn2.createStatement(); // 创建Statement
                st2.executeQuery("select 1 from dual"); // 创建ResultSet
            }
        } catch (Exception e) {
            System.err.println("创建Statement，i==" + i);
            e.printStackTrace();
        } finally {
            try {
                if (conn2 != null)
                    conn2.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        // 三、循环创建Connection
        i = 0;
        try {
            // 加载驱动
            Class.forName(driveClassOracle);

            while (true) {
                i++;
                if (i % 100 == 0) {
                    System.out.println("创建Connection，i==" + i);
                }
                DriverManager.getConnection(urlOracle, userOracle, passwordOracle);// 创建Connection
            }
        } catch (Exception e) {
            System.err.println("创建Connection，i==" + i);
            e.printStackTrace();
        }

    }

    /**
     * <h1>事物开启、提交、回滚研究</h1><br/>
     * <h1>脏读研究</h1><br/>
     *
     * <b>结论:</b>
     * 在一个事务中，如果第1、3个SQL有正确，第2个SQL有错误，那么<br/>
     * 1、Mysql：	执行conn.rollback()，这3个SQL都会失败；没有执行conn.rollback()，这3个SQL都会失败。<br/>
     * 2、Oracle：执行conn.rollback()，这3个SQL都会失败；没有执行conn.rollback()且没有执行conn.close()，这3个SQL都会失败；没有执行conn.rollback()但执行conn.close()第1个SQL成功，第2、3个SQL失败。<br/>
     * <b>也就是说Oracle在conn.close()时不会将“已经执行st.executeBatch()或st.execute()但未conn.rollback()”的SQL回滚。针对testOracle7()方法，第1个SQL会落库，第2个失败、导致第3个也失败</b><br/>
     * <b>也就是说Mysql在conn.close()时会将“已经执行st.executeBatch()或st.execute()但未conn.rollback()”的SQL回滚。</b><br/>
     */
    private void testMysql7() {
        System.out.println(getCurMethodName(Thread.currentThread().getStackTrace()[1]));

        Connection conn = null;
        Statement st = null;
        try {
            // 1 加载驱动
            Class.forName(driveClassMysql);

            // 2 创建连接
            conn = DriverManager.getConnection(urlMysql, userMysql, passwordMysql);
            conn.setAutoCommit(false); // 开启事务（取消自动提交事务）

            st = conn.createStatement();

            // 3 清表
            st.execute("truncate table class_innodb");

            // 4 插入：
            String sSql1 = "insert into class_innodb (classid,classname,remark) values ('stu001','张三','sSql3')";
            String sSql2 = "insert into (classid,classname,remark) values ('stu002','张三','sSql3')";  // 造异常
            String sSql3 = "insert into class_innodb (classid,classname,remark) values ('stu003','张三','sSql3')";
            System.out.println("sSql1:" + sSql1);
            System.out.println("sSql2:" + sSql2);
            System.out.println("sSql3:" + sSql3);
            st.addBatch(sSql1);
            st.addBatch(sSql2);
            st.addBatch(sSql3);
            int[] count = st.executeBatch();
            conn.commit();
            for (int i = 0; i < count.length; i++) {
                System.out.println("count[" + i + "]" + count[i]);

            }

        } catch (Exception e) {
            e.printStackTrace();
            try {
                conn.rollback(); // Mysql不需要回滚。Oracle需要回滚，坑爹，全写上就是了
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            try {
                if (st != null)
                    st.close();
                if (conn != null)
                    conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * <h1>事物开启、提交、回滚研究</h1><br/>
     * <h1>脏读研究</h1><br/>
     *
     * <b>结论:</b>
     * 在一个事务中，如果第1、3个SQL有正确，第2个SQL有错误，那么<br/>
     * 1、Mysql：	执行conn.rollback()，这3个SQL都会失败；没有执行conn.rollback()，这3个SQL都会失败。<br/>
     * 2、Oracle：执行conn.rollback()，这3个SQL都会失败；没有执行conn.rollback()且没有执行conn.close()，这3个SQL都会失败；没有执行conn.rollback()但执行conn.close()第1个SQL成功，第2、3个SQL失败。<br/>
     * <b>也就是说Oracle在conn.close()时不会将“已经执行st.executeBatch()或st.execute()但未conn.rollback()”的SQL回滚。针对testOracle7()方法，第1个SQL会落库，第2个失败、导致第3个也失败</b><br/>
     * <b>也就是说Mysql在conn.close()时会将“已经执行st.executeBatch()或st.execute()但未conn.rollback()”的SQL回滚。</b><br/>
     */
    private void testOracle7() {
        System.out.println(getCurMethodName(Thread.currentThread().getStackTrace()[1]));

        Connection conn = null;
        Statement st = null;
        try {
            // 1 加载驱动
            Class.forName(driveClassMysql);

            // 2 创建连接
            conn = DriverManager.getConnection(urlOracle, userOracle, passwordOracle);
            conn.setAutoCommit(false); // 开启事务

            st = conn.createStatement();

            // 3 清表
            st.execute("truncate table class_innodb");

            // 4 插入：
            String sSql1 = "insert into class_innodb (classid,classname,remark) values ('stu001','张三','sSql3')";
            String sSql2 = "insert into (classid,classname,remark) values ('stu002','张三','sSql3')";
            String sSql3 = "insert into class_innodb (classid,classname,remark) values ('stu003','张三','sSql3')";
            System.out.println("sSql1:" + sSql1);
            System.out.println("sSql2:" + sSql2);
            System.out.println("sSql3:" + sSql3);
            st.addBatch(sSql1);
            st.addBatch(sSql2);
            st.addBatch(sSql3);
            int[] count = st.executeBatch();
            conn.commit();
            for (int i = 0; i < count.length; i++) {
                System.out.println("count[" + i + "]" + count[i]);

            }

        } catch (Exception e) {
            e.printStackTrace();
            try {
                conn.rollback(); // Mysql不需要回滚。Oracle需要回滚，坑爹，全写上就是了
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            try {
                if (st != null)
                    st.close();
                if (conn != null)
                    conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * <h1>模拟转账业务,测试事务</h1><br/>
     * 假设此时用户已经登录了网银, 并且已经输入了收款方账号和 转账的金额,点击了转账<br/>
     * <br/>
     * 转账的步骤: <br/>
     * 1.验证收款方账号是否存在(查询)<br/>
     * 2.验证付款方余额是否够用(查询) <br/>
     * 3.将付款方余额-N元(修改)<br/>
     * 4.将收款方余额+N元(修改)<br/>
     */
    private void testMysql8() {
        System.out.println(getCurMethodName(Thread.currentThread().getStackTrace()[1]));

        // 假设用户输入的信息如下
        // 付款方账号
        String payId = "00001";
        // 收款方账号
        String recId = "00002";
        // 转账的金额
        double money = 1000.0;

        // 转账是一个完整的业务流程,必须保证 它的完整性,即该流程应处于一个事务之内,所以创建一个连接.
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(urlMysql, userMysql, passwordMysql);
            conn.setAutoCommit(false); // 开启事务（取消自动提交事务）

            // 1.查询收款方账号并验证
            String sql = "select * from AccoutInfo where CardId=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, recId);
            ResultSet rs = ps.executeQuery();
            if (!rs.next()) {
                throw new SQLException("收款方账号不存在");
            }
            double recMoney = rs.getDouble("money");
            // 2.查询付款方余额并验证
            String sql2 = "select * from AccoutInfo where CardId=?";
            PreparedStatement ps2 = conn.prepareStatement(sql2);
            ps2.setString(1, payId);
            ResultSet rs2 = ps2.executeQuery();
            double payMoney = 0.0;
            if (rs2.next()) {
                payMoney = rs2.getDouble("money");
                if (payMoney < money) {
                    throw new SQLException("余额不足");
                }
            }
            // 3.修改付款方余额
            String sql3 = "update AccoutInfo set money=? where CardId=?";
            PreparedStatement ps3 = conn.prepareStatement(sql3);
            ps3.setDouble(1, payMoney - money);
            ps3.setString(2, payId);
            ps3.executeUpdate();

            Integer.valueOf("断电了");

            // 4.修改收款方余额
            String sql4 = "update AccoutInfo set money=? where CardId=?";
            PreparedStatement ps4 = conn.prepareStatement(sql4);
            ps4.setDouble(1, recMoney + money);
            ps4.setString(2, recId);
            ps4.executeUpdate();

            conn.commit(); // 转账是一个完整的过程,只需要在整个流程完成后,提交一次事务即可。
        } catch (Exception e) {
            e.printStackTrace();
            try {
                conn.rollback(); // Mysql不需要回滚。Oracle需要回滚，坑爹，全写上就是了
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * <h1>模拟转账业务,测试事务</h1><br/>
     * 假设此时用户已经登录了网银, 并且已经输入了收款方账号和 转账的金额,点击了转账<br/>
     * <br/>
     * 转账的步骤: <br/>
     * 1.验证收款方账号是否存在(查询)<br/>
     * 2.验证付款方余额是否够用(查询) <br/>
     * 3.将付款方余额-N元(修改)<br/>
     * 4.将收款方余额+N元(修改)<br/>
     */
    private void testOracle8() {
        System.out.println(getCurMethodName(Thread.currentThread().getStackTrace()[1]));

        // 假设用户输入的信息如下
        // 付款方账号
        String payId = "00001";
        // 收款方账号
        String recId = "00002";
        // 转账的金额
        double money = 1000.0;

        // 转账是一个完整的业务流程,必须保证 它的完整性,即该流程应处于一个事务之内,所以创建一个连接.
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(urlOracle, userOracle, passwordOracle);
            conn.setAutoCommit(false); // 取消自动提交事务,开启事务

            // 1.查询收款方账号并验证
            String sql = "select * from AccoutInfo where CardId=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, recId);
            ResultSet rs = ps.executeQuery();
            if (!rs.next()) {
                throw new SQLException("收款方账号不存在");
            }
            double recMoney = rs.getDouble("money");
            // 2.查询付款方余额并验证
            String sql2 = "select * from AccoutInfo where CardId=?";
            PreparedStatement ps2 = conn.prepareStatement(sql2);
            ps2.setString(1, payId);
            ResultSet rs2 = ps2.executeQuery();
            double payMoney = 0.0;
            if (rs2.next()) {
                payMoney = rs2.getDouble("money");
                if (payMoney < money) {
                    throw new SQLException("余额不足");
                }
            }
            // 3.修改付款方余额
            String sql3 = "update AccoutInfo set money=? where CardId=?";
            PreparedStatement ps3 = conn.prepareStatement(sql3);
            ps3.setDouble(1, payMoney - money);
            ps3.setString(2, payId);
            ps3.executeUpdate();

            Integer.valueOf("断电了");

            // 4.修改收款方余额
            String sql4 = "update AccoutInfo set money=? where CardId=?";
            PreparedStatement ps4 = conn.prepareStatement(sql4);
            ps4.setDouble(1, recMoney + money);
            ps4.setString(2, recId);
            ps4.executeUpdate();

            conn.commit(); // 转账是一个完整的过程,只需要在整个流程完成后,提交一次事务即可。
        } catch (Exception e) {
            e.printStackTrace();
            try {
                conn.rollback(); // Mysql不需要回滚。Oracle需要回滚，坑爹，全写上就是了
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 脏读研究1
     */
    private void testMysql9() {
        System.out.println(getCurMethodName(Thread.currentThread().getStackTrace()[1]));

        Connection conn = null;
        Statement st = null;
        try {
            // 1 加载驱动
            Class.forName(driveClassMysql);

            // 2 创建连接
            conn = DriverManager.getConnection(urlMysql, userMysql, passwordMysql);
            conn.setAutoCommit(false); // 开启事务

            st = conn.createStatement();

            // 3 清表
            st.execute("truncate table student");

            // 4 插入：
            String sSql1 = "insert into student (studentid,studentname,remark) values ('stu001','张三','sSql3')";
            String sSql2 = "insert into studen (studentid,studentname,remark) values ('stu002','张三','sSql3')";
            String sSql3 = "insert into student (studentid,studentname,remark) values ('stu003','张三','sSql3')";
            System.out.println("sSql1:" + sSql1);
            System.out.println("sSql2:" + sSql2);
            System.out.println("sSql3:" + sSql3);
            st.addBatch(sSql1);
            st.addBatch(sSql2);
            st.addBatch(sSql3);
            int[] count = st.executeBatch();
            conn.commit();
            for (int i = 0; i < count.length; i++) {
                System.out.println("count[" + i + "]" + count[i]);

            }

        } catch (Exception e) {
            e.printStackTrace();
            try {
                conn.rollback(); // Mysql不需要回滚。Oracle需要回滚，坑爹，全写上就是了
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            try {
                if (st != null)
                    st.close();
                if (conn != null)
                    conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    private void testMysql10() {
        System.out.println(getCurMethodName(Thread.currentThread().getStackTrace()[1]));

        long startTime;
        long endTime;
        long startTimeIn;
        long endTimeIn;

        Connection conn1 = null;
        Statement st1 = null;
        int count = 10000000;
        try {

            // 1 加载驱动
            Class.forName(driveClassMysql);
            // 2 创建Connection和Statement

            // Mysql开启批量rewriteBatchedStatements（扩展语句）
            conn1 = DriverManager.getConnection(urlMysqlBatch, userMysql, passwordMysql);
            conn1.setAutoCommit(false); // ***此行对于不支持事物的MyISAM、Memory引擎无效
            st1 = conn1.createStatement();

            // 4 开始测试
            String sSql3 = "insert into student (studentid,studentname,remark) values ('stu001','张三','sSql3')";

            // 4.3 InnoDB
            // 4.3.1 开批量rewriteBatchedStatements
            st1.execute("truncate table student"); // 对于不支持事务的引擎连接（或者支持事务但是自动提交的连接），此行代码会数据落库,对于truncate
            // table也会立即执行
            startTime = System.currentTimeMillis();
            startTimeIn = startTime;
            for (int i = 1; i <= count; i++) {
                st1.addBatch(sSql3.replace("stu001", "stu" + i).replace("张三", "张三" + i));
                if (i % 100000 == 0) {
                    System.out.println(i);
                    st1.executeBatch(); // 对于不支持事务的引擎，不管是否自动提交，此行代码会数据落库
                    conn1.commit(); // 对于支持事务的引擎，执行st.executeBatch()不会提交，conn.commit()才会提交
                    endTimeIn = System.currentTimeMillis();
                    System.out.println(sdf.format(new Date()) + "	InnoDB开批量第" + i + "条，用时" + (endTimeIn - startTimeIn) + "毫秒");
                    startTimeIn = endTimeIn;
                    st1.clearBatch();
                }
            }
            conn1.commit();
            endTime = System.currentTimeMillis();
            System.out.println(sdf.format(new Date()) + "	InnoDB开批量共" + count + "条，用时" + (endTime - startTime) + "毫秒");

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (st1 != null)
                    st1.close();
                if (conn1 != null)
                    conn1.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 获取当前方法名
     *
     * @return 方法名
     */
    private String getCurMethodName(StackTraceElement StackTrace) {
        return "方法== " + StackTrace.getMethodName() + " ===================================================================================================================";
    }

}
